package com.sbtr.util;

import com.sbtr.business.datainfo.entity.DbConnection;
import com.sbtr.business.datainfo.mapper.SQLRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.jdbc.datasource.SimpleDriverDataSource;
import org.springframework.jdbc.datasource.SingleConnectionDataSource;
import org.springframework.stereotype.Component;
import org.springframework.transaction.support.TransactionTemplate;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * <p>
 * 数据库连接工具类
 * </p>
 *

 */
@Component
public class ConnectionUtils {
	public static final String HIVE = "hive";
	public static final String MYSQL = "mysql";
	public static final String SELECT = "select";
	public static final String LIMIT = "limit";

	public JdbcTemplate getConnection(DataSource src) {
		return new JdbcTemplate((src));
	}

	public NamedParameterJdbcTemplate getNamedParamConnection(DataSource src) {
		return new NamedParameterJdbcTemplate((src));
	}

	public DataSource getHiveDataDource(DbConnection config) throws ClassNotFoundException {
		Class<? extends Driver> driver = (Class<? extends Driver>) Class.forName(config.getDriverClassName());
		SimpleDriverDataSource simpleDriverDataSource = new SimpleDriverDataSource();
		simpleDriverDataSource.setDriverClass(driver);
		simpleDriverDataSource.setUrl(config.getUrl());
		simpleDriverDataSource.setUsername(config.getUsername());
		simpleDriverDataSource.setPassword(config.getPassword());
		return simpleDriverDataSource;
	}

	public DataSource getDataDource(DbConnection config) {
		DriverManagerDataSource driverManagerDataSource = new DriverManagerDataSource(config.getUrl(),
				config.getUsername(), config.getPassword());
		return driverManagerDataSource;
	}

	public TransactionTemplate getTransactionConnection(DataSource src) {
		DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager(
				src);
		return new TransactionTemplate(dataSourceTransactionManager);
	}

	public Connection getJdbcConnection(DbConnection config) throws SQLException {
		SingleConnectionDataSource singleConnectionDataSource = new SingleConnectionDataSource(config.getUrl(),
				config.getUsername(), config.getPassword(), false);
		return singleConnectionDataSource.getConnection();
	}

	public <T> List<T> getItemsInTable(String tableName, RowMapper<T> rowMapper, DbConnection connectionConfig)
			throws SQLException {
		DataSource dataDource = null;
		if (MYSQL.equals(connectionConfig.getDbType().toLowerCase())) {
			dataDource = getDataDource(connectionConfig);
		} else if (HIVE.equals(connectionConfig.getDbType().toLowerCase())) {
			try {
				dataDource = getHiveDataDource(connectionConfig);
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				throw new RuntimeException("未找到合适的驱动");
			}
		} else {
			throw new RuntimeException("数据库类型不支持");
		}

		JdbcTemplate connection = getConnection(dataDource);
		List<T> result = connection.query("select * from " + tableName, rowMapper);
		dataDource.getConnection().close();
		return result;
	}

	public <T> List<T> getItemsInTableLimit(String tableName, RowMapper<T> rowMapper, DbConnection connectionConfig,
			int limitNum) throws SQLException {
		DataSource dataDource = null;
		if (MYSQL.equals(connectionConfig.getDbType().toLowerCase())) {
			dataDource = getDataDource(connectionConfig);
		} else if (HIVE.equals(connectionConfig.getDbType().toLowerCase())) {
			try {
				dataDource = getHiveDataDource(connectionConfig);
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				throw new RuntimeException("未找到合适的驱动");
			}
		} else {
			throw new RuntimeException("数据库类型不支持");
		}
		JdbcTemplate connection = getConnection(dataDource);
		StringBuilder sb = new StringBuilder();
		sb.append("select * from ").append(tableName).append(" limit ").append(limitNum);
		List<T> queryForRowSet = connection.query(sb.toString(), rowMapper);
		dataDource.getConnection().close();
		return queryForRowSet;
	}

	public List<Map<String, Object>> getItemsInTable(String tableName, DbConnection connectionConfig)
			throws ClassNotFoundException, SQLException {
		DataSource dataDource = null;
		if (MYSQL.equals(connectionConfig.getDbType().toLowerCase())) {
			dataDource = getDataDource(connectionConfig);
		} else if (HIVE.equals(connectionConfig.getDbType().toLowerCase())) {
			dataDource = getHiveDataDource(connectionConfig);
		} else {
			throw new RuntimeException("数据库类型不支持");
		}
		JdbcTemplate connection = getConnection(dataDource);
		List<Map<String, Object>> result = connection.queryForList("select * from " + tableName);
		dataDource.getConnection().close();
		return result;
	}

	public List<Map<String, Object>> getItemsInTableLimit(String tableName, DbConnection connectionConfig, int limitNum)
			throws SQLException {
		DataSource dataDource = null;
		if (MYSQL.equals(connectionConfig.getDbType().toLowerCase())) {
			dataDource = getDataDource(connectionConfig);
		} else if (HIVE.equals(connectionConfig.getDbType().toLowerCase())) {
			try {
				dataDource = getHiveDataDource(connectionConfig);
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				throw new RuntimeException("未找到合适的驱动");
			}
		} else {
			throw new RuntimeException("数据库类型不支持");
		}
		JdbcTemplate connection = getConnection(dataDource);
		StringBuilder sb = new StringBuilder();
		sb.append("select * from ").append(tableName).append(" limit ").append(limitNum);
		List<Map<String, Object>> queryForRowSet = connection.queryForList(sb.toString());
		dataDource.getConnection().close();
		return queryForRowSet;
	}

	public <T> List<T> getItemsBySql(String sql, RowMapper<T> rowMapper, DbConnection connectionConfig)
			throws SQLException {
		DataSource dataDource = null;
		if (MYSQL.equals(connectionConfig.getDbType().toLowerCase())) {
			dataDource = getDataDource(connectionConfig);
		} else if (HIVE.equals(connectionConfig.getDbType().toLowerCase())) {
			try {
				dataDource = getHiveDataDource(connectionConfig);
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				throw new RuntimeException("未找到合适的驱动");
			}
		} else {
			throw new RuntimeException("数据库类型不支持");
		}
		JdbcTemplate connection = getConnection(dataDource);
		List<T> result = connection.query(sql, rowMapper);
		dataDource.getConnection().close();
		return result;
	}

	public List<Map<String, Object>> executionSql(String sql, DbConnection connectionConfig) throws SQLException {
		DataSource dataDource = null;
		if (MYSQL.equals(connectionConfig.getDbType().toLowerCase())) {
			dataDource = getDataDource(connectionConfig);
		} else if (HIVE.equals(connectionConfig.getDbType().toLowerCase())) {
			try {
				dataDource = getHiveDataDource(connectionConfig);
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				throw new RuntimeException("未找到合适的驱动");
			}
		} else {
			throw new RuntimeException("数据库类型不支持");
		}
		JdbcTemplate connection = getConnection(dataDource);
		// List<Map<String, Object>> list = connection.queryForList(sql);
		List<Map<String, Object>> list = connection.query(sql, new SQLRowMapper());
		dataDource.getConnection().close();
		return list;
	}

}
